library(styler)
install.packages("styler")
source("connect_with_database.R")

# connect database
ods <- est_psql_conn(db$ods)
oct <- est_psql_conn(db$oct)

# Get data----------------------------------------------------
student_limit <- 20

# set plot theme
old <- theme_set(theme_tufte() +
  theme(
    text = element_text(family = "Menlo"),
    legend.text = element_text(size = 16, color = "black", face = "bold")
  ))

theme_set(old)


o_teacher <- tbl(oct, "o_teacher")
o_homework_answer <- tbl(oct, "o_homework")
im_notice <- tbl(ods, "im_notice")


# Get data----------------------------
# teacher geo information
teacher <- o_teacher %>%
  select(teacher_id,
    school_id,
    register_day = day,
    student_count,
    county_id
  ) %>%
  filter(student_count >= student_limit, county_id > 0, register_day >= "2018-09-01")

# teacher active information  
homework <- o_homework %>%
  filter(homework_type_code %in% c("All", "Review", "Ocr", "OnlineMatch", "PersonalReading"), day >= "2018-09-01") %>%
  select(teacher_id, day) %>%
  distinct()

notice <- im_notice %>%
  select(
    teacher_id = fromteacherid,
    starttime
  ) %>%
  distinct()

teacher_homework <- teacher %>%
  inner_join(homework, by = "teacher_id") %>%
  collect()

# set copy = TRUE to join data from different databases
teacher_notice <- teacher %>% 
  inner_join(notice, by = "teacher_id", copy = TRUE) %>% 
  collect()


